--Api 表
/*
select * from SYS_Apis
select * from SYS_ApiM --M  Memo Main Manage

ReturnType
返回类型（类似返回值）
1 execSql 
2 openSql
3 execProc(不带返回结果) 
4 openProc(带返回结果集)
5 getList (使用getList sql 模板)
*/

declare 
  @ApiName varchar(50)='wp.tplTree',
  @ReturnType int =5
--
--
delete from SYS_Apis where ApiName=@ApiName
delete from SYS_ApiM where apiName=@ApiName
insert into SYS_ApiM([apiName],[name],[memo])
select @ApiName,'根据模板来源获取底稿模板树','根据模板来源获取底稿模板树tree, 在新建年度底稿中使用'
insert into SYS_Apis(ApiName,paramsLen,params,ReturnType,sqlText)
select @ApiName,0,
null,
@ReturnType,
'declare @dgmbTree table(nType int,id nvarchar(255),pid nvarchar(255),dgnd nvarchar(50),dgmc nvarchar(90),dglx varchar(10),dgSyh varchar(50),
lx varchar(50),mustDo bit,code nvarchar(255),dgbyuser varchar(50))
#if(dbname)
insert into @dgmbTree select 1,dgnd,null,dgnd,dgnd+''年度审计底稿'',''nd'',null,null,null,0,NULL from [#(dbname)]..sjjd group by dgnd 
insert into @dgmbTree select 2,jdid,dgnd+isnull(pjdmc,''''),dgnd,jdmc,jdlx,Code,null,null,jdid,NULL from [#(dbname)]..sjjd where 1=1  
insert into @dgmbTree select 3,dgid,jdid,dgnd,dgmc,dglx,dgSyh,lx,1,dgid,dgbyuser from [#(dbname)]..sjdg A
where (IsNull(dglb,''3'')=''3'' Or (dglb=''6''
and not Exists(select LinkDGID from [#(dbname)]..fjDGlinkinfo where IsNull(IsDel,0)=0 and LinkDGID=a.dgid)))
and isnull(dgzt,0)<>999
#else
insert into @dgmbTree select 2,jdmc,Pjdmc,mbmc,jdmc,jdlx,null,null,0,Code,NULL from mbsjjd 
where mbmc=#para(tplName) order by Code
insert into @dgmbTree select 3,dgmc,jdmc,mbmc,dgmc,dglx,dgSyh,lx,MustDo,0,NULL from mbsjdg where mbmc=#para(tplName)
#end
select * from @dgmbTree order by Pid,dgSyh,Code,dgnd,dgmc
'
